<?php
include_once('include/Config.php');

Class ReporteSmgDAL {

    private $link;

    public function __construct() {
        $this->conectar();
    }

    private function conectar() {
        $this->link = $GLOBALS["DBConnPDO"];
    }

    public function obtenerReporteArrayTxt($where) {
        $sql = "select 
                ep.DescBreve as 'cod_ramo',
                c.Apellido,
                c.Nombre,
                c.idTipoDocumento,
                td.DescBreve as TipoDocumento,
                c.NumeroDocumento,
                c.Calle,
                c.Numero,
                c.Piso,
                c.Depto,
                c.CodigoPostal,
                c.idLocalidad as 'localidad',
                p.DescBreve as 'provincia',
                c.telefono,
                c.Telefono2,
                c.Email,
                c.Sexo,
                '' as 'EstadoCivil',
                '' as 'tipoIva',
                tt.DescBreve as TipoTarjeta,
                ct.NumeroTarjeta,
                ct.VencimientoTarjeta as 'VigDesde',
                ct.VencimientoTarjeta as 'VigHasta',
                '' as 'Codexterno',
                ta.Descripcion as TipoActividad,
                '' as 'tipovivienda',
                '' as 'Calleriesgo',
                '' as 'CodNumRiesgo',
                '' as 'PisoRiesgo',
                '' as 'CodExterno',
                '' as 'DeptoExterno',
                '' as 'CPRiesgo',
                '' as 'LocRiesgo',
                '' as 'ProvinciaRiesgo',
                '' as 'CodPlan',
                '' as 'CodAgenteSMG',
                '' as 'ApellidoNomina',
                '' as 'NombreNomina',
                '' as 'CodDocNomina',
                '' as 'NumDocNomina',
                '' as 'FecnacNomina',
                '' as 'BeneficioNomina',
                '' as 'BeneficioDoc'
                from solicitud s
                inner join empresaproducto ep on ep.idProducto = s.idProducto and ep.idEmpresa = s.idEmpresa
                inner join cliente c on c.idCliente = s.idCliente
                inner join empresaprovincia p on p.idProvincia = c.idProvincia and p.idEmpresa = s.idEmpresa
                inner join empresalocalidad l on l.idLocalidad = c.idLocalidad and l.idProvincia = p.idProvincia and l.idEmpresa = s.idEmpresa
                inner join empresatipodocumento td on td.idTipoDocumento = c.idTipoDocumento and td.idEmpresa = s.idEmpresa
                inner join empresatipoactividad ta on ta.idTipoActividad = c.idTipoActividad and td.idEmpresa = s.idEmpresa
                inner join clientetarjeta ct on c.idCliente = ct.idCliente
                inner join empresatipotarjeta tt on tt.idTipoTarjeta = ct.idTipoTarjeta and tt.idEmpresa = s.idEmpresa
                ";

        $sql .= $where;

				//echo $sql;exit;

        $lista = array();
        foreach ($this->link->query($sql) as $row) {
            $cod_ramo = $row['cod_ramo'] . ";";
            $apellido = $row['Apellido'] . ";";
            $nombre = $row['Nombre'] . ";";
            //$idTipoDocumento = $row['idTipoDocumento'] . ";";
            $TipoDocumento = $row['TipoDocumento'] . ";";
            $NumeroDocumento = $row['NumeroDocumento'] . ";";
            $Calle = $row['Calle'] . ";";
            $Numero = $row['Numero'] . ";";
            $piso = $row['Piso'] . ";";
            $Depto = $row['Depto'] . ";";
            $CodigoPostal = $row['CodigoPostal'] . ";";
            $localidad = $row['localidad'] . ";";    
            $provincia = $row['provincia'] . ";";   
            $telefono = $row['telefono'] . ";";
            $Telefono2 = $row['Telefono2'] . ";";
            $Email = $row['Email'] . ";";
            $Sexo = $row['Sexo'] . ";";
            $EstadoCivil = "2"; //$row['EstadoCivil'] . ";";
            $tipoIva = "1"; //$row['tipoIva'] . ";";
            $TipoTarjeta = $row['TipoTarjeta'] . ";";
            $NumeroTarjeta = $row['NumeroTarjeta'] . ";";
            //$VigDesde = $row['VigDesde'] . ";";
            //$VigHasta = $row['VigHasta'] . ";";
            $VigDesde = date("Ymd") . ";";
            $VigHasta = Date('Ymd', strtotime("+1 years")) . ";";            
            //$Codexterno = $row['Codexterno'] . ";";
            $Codexterno = "777777;";
            
            $TipoActividad = "";//$row['TipoActividad'] . ";";
            if($row['cod_ramo'] == 12) $TipoActividad = "474";
            
            $tipovivienda = $row['tipovivienda'] . ";";
            $Calleriesgo = $row['Calleriesgo'] . ";";
            $CodNumRiesgo = $row['CodNumRiesgo'] . ";";
            $PisoRiesgo = $row['PisoRiesgo'] . ";";
            $CodExterno = $row['CodExterno'] . ";";
            $DeptoExterno = $row['DeptoExterno'] . ";";
            $CPRiesgo = $row['CPRiesgo'] . ";";
            $LocRiesgo = $row['LocRiesgo'] . ";";
            $ProvinciaRiesgo = $row['ProvinciaRiesgo'] . ";";            
            $CodPlan = $row['CodPlan'] . ";";
            $CodAgenteSMG = $row['CodAgenteSMG'] . ";";
            $ApellidoNomina = $row['ApellidoNomina'] . ";";
            $NombreNomina = $row['NombreNomina'] . ";";
            $CodDocNomina = $row['CodDocNomina'] . ";";
            $NumDocNomina = $row['NumDocNomina'] . ";";
            $FecnacNomina = $row['FecnacNomina'] . ";";
            $BeneficioNomina = $row['BeneficioNomina'] . ";";            
            $BeneficioDoc = $row['BeneficioDoc'] . ";";

            $linea = $cod_ramo.$apellido.$nombre.$TipoDocumento.$NumeroDocumento.$Calle.$Numero.$piso.$Depto.$CodigoPostal.$localidad.$provincia;   
            $linea .= $telefono.$Telefono2.$Email.$Sexo.$EstadoCivil.$tipoIva.$TipoTarjeta.$NumeroTarjeta.$VigDesde.$VigHasta.$Codexterno;
            $linea .=$TipoActividad.$tipovivienda.$Calleriesgo.$CodNumRiesgo.$PisoRiesgo.$CodExterno.$DeptoExterno.$CPRiesgo;
            $linea .=$LocRiesgo.$ProvinciaRiesgo.$CodPlan.$CodAgenteSMG.$ApellidoNomina.$NombreNomina.$CodDocNomina.$NumDocNomina.$FecnacNomina.$BeneficioNomina.$BeneficioDoc;
            
            $lista[] = $linea;
        }
        return $lista;
    }

    public function obtenerReporteArrayExcel($where) {
        
        $sql = "select s.numSolicitud, 
                DATE_FORMAT(s.FechaSolicitud, '%d/%m/%Y') as 'FechaSol', 
                concat(ct.NumeroTarjeta, ' ') as 'NumeroTarjeta' , 
                cast(ct.VencimientoTarjeta as char) as 'VencimientoTarjeta',  
                tt.Descripcion AS 'MarcaTarjeta', 
                ct.CodVerificacion, 
                c.NumeroDocumento, 
                DATE_FORMAT(c.FechaNacimiento, '%d/%m/%Y') as 'FechaNac', 
                'prodcuto', concat(c.Apellido, ', ', c.Nombre) as 'ApellidoNOmbre', 
                concat(c.Calle, ', ', c.Numero ) as 'CalleNumero', 
                concat(c.Piso, ' - ', c.Depto ) as 'PisoDepto', 
                l.Descripcion as 'Localidad', 
                p.Descripcion as 'Provincia', 
                c.CodigoPostal, 
                '' as 'CodArea', 
                c.Telefono, c.Telefono2, c.Email, 
                '' as 'Profesion', 
                '' as 'EstadoCivil', 
                '' as 'CALL', 
                u.NombreApellido, 
                '' as 'Legajo', 
                '' as 'Productor', 
                s.MontoPoliza as 'SumaAsegurada', 
                '' as 'Premio', 
                '' as 'clarin' 
                from solicitud s 
                inner join clientetarjeta ct on s.idCliente = ct.idCliente 
                inner join empresatipotarjeta tt on tt.idTipoTarjeta = ct.idTipoTarjeta and tt.idEmpresa = s.idEmpresa
                inner join cliente c on s.idCliente = c.idCliente 
                inner join empresaprovincia p on p.idProvincia = c.idProvincia and p.idEmpresa = s.idEmpresa
                inner join empresalocalidad l on l.idLocalidad = c.idLocalidad and l.idProvincia = p.idProvincia and l.idEmpresa = s.idEmpresa
                inner join usuario u on u.idUsuario = s.idUsuario ";

        $sql .= $where;

        $stmt = $this->link->prepare($sql);

        $stmt->execute();
        if ($stmt->errorCode() != 0) {
            $errors = $stmt->errorInfo();
        }
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $result;
    }

}

?>
